<?php
declare (strict_types = 1);

namespace app\controller;

use app\common\BaseController;
use app\model\House;
use app\service\GoodsService;
use app\service\HouseInService;
use app\service\HouseOutService;
use app\service\HouseService;
use app\service\InnerPurchaseService;
use app\service\StoreBackService;
use app\service\StoreService;
use app\service\UserService;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\facade\Db;
use think\Request;

class ExportController extends BaseController
{
  /**
   * 导出进销存报表
   *
   * @param  \think\Request  $request
   * @return \think\Response
   */
  public function inout(Request $request) {
    $month = $request->param('month', '');
    if (!$month) {
      $this->send400Response('请选择月份');
    }
    // 该月份的1号（当天）数据
    $monthBegin = date('Y-m-01', strtotime($month));
    // 该月份的下个月1号（当天）数据
    $monthEnd = date('Y-m-01', strtotime('next month', strtotime($month)));
    // ==== 该月的月尾数据 ====
    $house = Db::table('house_statistics')
      ->where('date', '=', $monthEnd)
      ->select()
      ->toArray();
    $house = array_column($house, null, 'gid');
    // ==== 月初的数据 ====
    $houseLast = Db::table('house_statistics')
      ->where('date', '=', $monthBegin)
      ->select()
      ->toArray();
    $houseLast = array_column($houseLast, null, 'gid');
    // ==== 该月的出库 ====
    $houseOut = [];
    $conditions1 = [['sale_time', $month.'%', 'LIKE'], ['status', 1, '=']];
    $res1 = HouseOutService::getInstance()->getHouseOutByCondition($conditions1);
    foreach ($res1 as $value1) {
      $totalSell = round($value1['count'] * $value1['sell_price'], 2);
      $totalCost = round($value1['count'] * $value1['price'], 2);
      if (isset($houseOut[$value1['goods_id']])) {
        $houseOut[$value1['goods_id']]['count'] += $value1['count'];
        $houseOut[$value1['goods_id']]['total_cost'] += $totalCost;
        $houseOut[$value1['goods_id']]['total_sell'] += $totalSell;
        $houseOut[$value1['goods_id']]['price'] = $houseOut[$value1['goods_id']]['count'] ? round($houseOut[$value1['goods_id']]['total_cost'] / $houseOut[$value1['goods_id']]['count'], 2) : 0;
      } else {
        $houseOut[$value1['goods_id']] = [
          'count' => $value1['count'],
          'price' => $value1['price'],
          'total_sell' => $totalSell,
          'total_cost' => $totalCost
        ];
      }
    }
    // ==== 该月的退货 ====
    $houseBack = [];
    $conditions2 = [['so.back_time', $month.'%', 'LIKE'], ['so.status', 1, '=']];
    $res2 = StoreBackService::getInstance()->getBackJoinStore($conditions2);
    foreach ($res2 as $value2) {
      $totalCost = round($value2['count'] * $value2['price'], 2);
      if (isset($houseBack[$value2['goods_id']])) {
        $houseBack[$value2['goods_id']]['count'] += $value2['count'];
        $houseBack[$value2['goods_id']]['total_cost'] += $totalCost;
        $houseBack[$value2['goods_id']]['price'] = $houseBack[$value2['goods_id']]['count'] ? round($houseBack[$value2['goods_id']]['total_cost'] / $houseBack[$value2['goods_id']]['count'], 2) : 0;
      } else {
        $houseBack[$value2['goods_id']] = [
          'count' => $value2['count'],
          'price' => $value2['price'],
          'total_cost' => round($value2['count'] * $value2['price'], 2)
        ];
      }
    }

    // ==== 该月的入库 ====
    $houseIn = [];
    $conditions3 = [['o.default_time', $month.'%', 'LIKE'], ['o.status', 1, '=']];
    $res3 = HouseInService::getInstance()->getHouseInJoinGoods($conditions3);
    foreach ($res3 as $value3) {
      if (isset($houseIn[$value3['goods_id']])) {
        $houseIn[$value3['goods_id']]['count'] += $value3['count'];
        $houseIn[$value3['goods_id']]['total_cost'] += $value3['total_amount'];
        $houseIn[$value3['goods_id']]['price'] = $houseIn[$value3['goods_id']]['count'] ? round($houseIn[$value3['goods_id']]['total_cost'] / $houseIn[$value3['goods_id']]['count'], 2) : 0;
      } else {
        $houseIn[$value3['goods_id']] = [
          'count' => $value3['count'],
          'price' => $value3['price'],
          'total_cost' => $value3['total_amount']
        ];
      }
    }
    $goodsList = GoodsService::getInstance()->getGoodsByCondition([], ['process' => 'asc', 'id' => 'asc']);
    $data = [];
    // 合计
    $totalData = [
      'name' => '',
      'unit' => '',
      'process' => '合计',
      'outCount' => 0,
      'outPrice' => 0,
      'outTotalCost' => 0,
      'outTotalSell' => 0,
      'profix' => 0,
      'inCount' => 0,
      'inPrice' => 0,
      'inTotalCost' => 0,
      'lastCount' => 0,
      'lastTotal' => 0,
      'lastCost' => 0,
      'Count' => 0,
      'Total' => 0,
      'Cost' => 0,
    ];

    foreach ($goodsList as $goods) {
      $add = [
        'name' => $goods['name'],
        'unit' => $goods['unit'],
        'process' => $goods['process'] ? '半成品' : '原材料',
        'outCount' => 0,
        'outPrice' => 0,
        'outTotalCost' => 0,
        'outTotalSell' => 0,
        'profix' => 0,
        'inCount' => 0,
        'inTotalCost' => 0,
        'inPrice' => 0,
        'lastCount' => 0,
        'lastTotal' => 0,
        'lastCost' => 0,
        'Count' => 0,
        'Total' => 0,
        'Cost' => 0,
      ];
      $in = []; // 入库
      $out = []; //出库
      $back = []; // 退货
      $end = []; // 月末数据
      $before = []; // 月初数据
      if (!isset($houseOut[$goods['id']]) && !isset($houseIn[$goods['id']]) && !isset($house[$goods['id']]) && !isset($houseLast[$goods['id']]) &&!isset($houseBack[$goods['id']])) {
        continue;
      }
      if (isset($houseOut[$goods['id']])) {
        $out = $houseOut[$goods['id']];
        $add['outCount'] = $out['count'];
        $add['outPrice'] = $out['price'];
        $add['outTotalCost'] = $out['total_cost'];
        $add['outTotalSell'] = $out['total_sell'];
        $add['profix'] = bcsub(strval($out['total_sell']), strval($out['total_cost']));
        // 合计
        $totalData['outCount'] += $out['count'];
        $totalData['outTotalCost'] += $out['total_cost'];
        $totalData['outTotalSell'] += $out['total_sell'];
      }
      if (isset($houseIn[$goods['id']])) {
        $in = $houseIn[$goods['id']];
        $add['inCount'] = $in['count'];
        $add['inPrice'] = $in['price'];
        $add['inTotalCost'] = $in['total_cost'];
        // 合计
        $totalData['inCount'] += $in['count'];
        $totalData['inTotalCost'] += $in['total_cost'];
      }
      if (isset($houseBack[$goods['id']])) {
        $back = $houseBack[$goods['id']];
        // 本期入库增加
        $add['inCount'] += $back['count'];
        $add['inTotalCost'] += $back['total_cost'];
        $add['inPrice'] = $back['count'] ? round($add['inTotalCost'] / $add['inCount'], 2) : 0;
        // 合计
        $totalData['inCount'] += $back['count'];
        $totalData['inTotalCost'] += $back['total_cost'];
      }
      if (isset($house[$goods['id']])) {
        $end = $house[$goods['id']];
        $add['Count'] = $end['count'];
        $add['Total'] = $end['total_cost'];
        $add['Cost'] = $end['count'] ? round($end['total_cost'] / $end['count'], 2) : 0;
        // 合计
        $totalData['Count'] += $end['count'];
        $totalData['Total'] += $end['total_cost'];
      }
      if (isset($houseLast[$goods['id']])) {
        $before = $houseLast[$goods['id']];
        $add['lastCount'] = $before['count'];
        $add['lastCost'] = $before['count'] ? round($before['total_cost'] / $before['count'], 2) : 0;
        $add['lastTotal'] = $before['total_cost'];
        // 合计
        $totalData['lastCount'] += isset($end['count']) ? $end['count'] : 0;
        $totalData['lastTotal'] += isset($end['total_cost']) ? $end['total_cost'] : 0;
      }
      $data[] = $add;
    }

    $totalData['outPrice'] = $totalData['outCount'] ? round($totalData['outTotalCost'] / $totalData['outCount'], 2) : 0;
    $totalData['inPrice'] = $totalData['inCount'] ? round($totalData['inTotalCost'] / $totalData['inCount'], 2) : 0;
    $totalData['profix'] = $totalData['outTotalSell'] - $totalData['outTotalCost'];
    $totalData['Cost'] = $totalData['Count'] ? round($totalData['Total'] / $totalData['Count'], 2) : 0;
    $totalData['lastCost'] = $totalData['lastCount'] ? round($totalData['lastTotal'] / $totalData['lastCount'], 2) : 0;
    $data[] = $totalData;

    // 导出数据
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(10);
    $sheet->getColumnDimension('B')->setWidth(20);
    $sheet->getColumnDimension('C')->setWidth(10);
    $sheet->getColumnDimension('D')->setWidth(10);
    $sheet->getColumnDimension('E')->setWidth(10);
    $sheet->getColumnDimension('F')->setWidth(10);
    $sheet->getColumnDimension('G')->setWidth(10);
    $sheet->getColumnDimension('H')->setWidth(10);
    $sheet->getColumnDimension('I')->setWidth(10);
    $sheet->getColumnDimension('J')->setWidth(10);
    $sheet->getColumnDimension('K')->setWidth(10);
    $sheet->getColumnDimension('L')->setWidth(10);
    $sheet->getColumnDimension('M')->setWidth(10);
    $sheet->getColumnDimension('N')->setWidth(10);
    $sheet->getColumnDimension('O')->setWidth(10);
    $sheet->getColumnDimension('P')->setWidth(10);
    $sheet->getColumnDimension('Q')->setWidth(10);
    $sheet->setCellValue('A1', '货物分类');
    $sheet->setCellValue('B1', '货物名称');
    $sheet->setCellValue('C1', '单位');
    $sheet->mergeCells('D1:F1');
    $sheet->setCellValue('D1', '期初结存');
    $sheet->mergeCells('G1:I1');
    $sheet->setCellValue('G1', '本期入库');
    $sheet->mergeCells('J1:N1');
    $sheet->setCellValue('J1', '本期发出');
    $sheet->mergeCells('O1:Q1');
    $sheet->setCellValue('O1', '期末结存');

    $sheet->setCellValue('A2', ' ');
    $sheet->setCellValue('B2', ' ');
    $sheet->setCellValue('C2', ' ');
    $sheet->setCellValue('D2', '数量');
    $sheet->setCellValue('E2', '金额');
    $sheet->setCellValue('F2', '平均单价');
    $sheet->setCellValue('G2', '数量');
    $sheet->setCellValue('H2', '金额');
    $sheet->setCellValue('I2', '平均单价');
    $sheet->setCellValue('J2', '数量');
    $sheet->setCellValue('K2', '成本单价');
    $sheet->setCellValue('L2', '成本金额');
    $sheet->setCellValue('M2', '销售金额');
    $sheet->setCellValue('N2', '利润');
    $sheet->setCellValue('O2', '数量');
    $sheet->setCellValue('P2', '金额');
    $sheet->setCellValue('Q2', '平均单价');
    $row = 2;
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", $value['process']);
      $sheet->setCellValue("B{$row}", $value['name']);
      $sheet->setCellValue("C{$row}", $value['unit']);
      $sheet->setCellValue("D{$row}", $value['lastCount']);
      $sheet->setCellValue("E{$row}", $value['lastTotal']);
      $sheet->setCellValue("F{$row}", $value['lastCost']);
      $sheet->setCellValue("G{$row}", $value['inCount']);
      $sheet->setCellValue("H{$row}", $value['inTotalCost']);
      $sheet->setCellValue("I{$row}", $value['inPrice']);
      $sheet->setCellValue("J{$row}", $value['outCount']);
      $sheet->setCellValue("K{$row}", $value['outPrice']);
      $sheet->setCellValue("L{$row}", $value['outTotalCost']);
      $sheet->setCellValue("M{$row}", $value['outTotalSell']);
      $sheet->setCellValue("N{$row}", $value['profix']);
      $sheet->setCellValue("O{$row}", $value['Count']);
      $sheet->setCellValue("P{$row}", $value['Total']);
      $sheet->setCellValue("Q{$row}", $value['Cost']);
    }

    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="冠记明细-进销存报表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 导出月头数据
   *
   * @param  \think\Request  $request
   * @return \think\Response
   */
  public function test() {
    // ==== 实际库存 ====
    $house = HouseService::getModel()
      ->where('status', '=', 1)
      ->select()
      ->toArray();
    $house = array_column($house, null, 'goods_id');
    // ==== 该月的出库 ====
    $houseOut = [];
    $conditions1 = [['status', 1, '=']];
    $res1 = HouseOutService::getInstance()->getHouseOutByCondition($conditions1);
    foreach ($res1 as $value1) {
      $totalCost = $value1['count'] * $value1['price'];
      if (isset($houseOut[$value1['goods_id']])) {
        $houseOut[$value1['goods_id']]['count'] += $value1['count'];
        $houseOut[$value1['goods_id']]['total_cost'] += $totalCost;
      } else {
        $houseOut[$value1['goods_id']] = [
          'count' => $value1['count'],
          'total_cost' => $totalCost
        ];
      }
    }
    // ==== 该月的入库 ====
    $houseIn = [];
    $conditions2 = [['o.status', 1, '=']];
    $res2 = HouseInService::getInstance()->getHouseInJoinGoods($conditions2);
    foreach ($res2 as $value2) {
      if (isset($houseIn[$value2['goods_id']])) {
        $houseIn[$value2['goods_id']]['count'] += $value2['count'];
        $houseIn[$value2['goods_id']]['total_cost'] += $value2['total_amount'];
      } else {
        $houseIn[$value2['goods_id']] = [
          'count' => $value2['count'],
          'total_cost' => $value2['total_amount']
        ];
      }
    }
    // ===== 商品表 ====
    $data = [];
    $goodsList = GoodsService::getInstance()->getGoodsByCondition();
    foreach ($goodsList as $good) {
      $add = [
        'goods_id' => $good['id'],
        'date' => '2022-09-30',
        'count' => isset($house[$good['id']]['count']) ? $house[$good['id']]['count'] : 0,
        'name' => $good['name'],
        'total_cost' => isset($house[$good['id']]['total_amount']) ? $house[$good['id']]['total_amount'] : 0
      ];
      if (isset($houseOut[$good['id']])) {
        $add['count'] += $houseOut[$good['id']]['count'];
        $add['total_cost'] += $houseOut[$good['id']]['total_cost'];
      }
      if (isset($houseIn[$good['id']])) {
        $add['count'] = bcsub(strval($add['count']), strval($houseIn[$good['id']]['count']));
        $add['total_cost'] = bcsub(strval($add['total_cost']), strval($houseIn[$good['id']]['total_cost']));
      }
      $data[] = $add;
    }
    // 导出数据
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(10);
    $sheet->getColumnDimension('B')->setWidth(10);
    $sheet->getColumnDimension('C')->setWidth(10);
    $sheet->getColumnDimension('D')->setWidth(10);
    $sheet->getColumnDimension('E')->setWidth(10);
    $sheet->setCellValue('A1', 'name');
    $sheet->setCellValue('B1', 'goods_id');
    $sheet->setCellValue('C1', 'date');
    $sheet->setCellValue('D1', 'count');
    $sheet->setCellValue('e1', 'total_cost');
    $row = 1;
    foreach($data as $value){
      $row++;
      $sheet->setCellValue("A{$row}", $value['name']);
      $sheet->setCellValue("B{$row}", $value['goods_id']);
      $sheet->setCellValue("C{$row}", $value['date']);
      $sheet->setCellValue("D{$row}", $value['count']);
      $sheet->setCellValue("E{$row}", $value['total_cost']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="insert.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  public function testBug() {
    $con1 = [
      ['o.status', 1, '=']
    ];
    $inner = InnerPurchaseService::getInstance()->getInnerJoinGoods($con1);

    $data = [];
    foreach ($inner as $value) {
      $str = $value['base'].'-'.$value['goods_id'];
      if (isset($data[$str])) {
        $data[$str] += $value['count'];
      } else {
        $data[$str] = $value['count'];
      }
    }
    $ids = [];
    $storeList = StoreService::getInstance()->getDataByCondition([]);
    $store = [];
    foreach ($storeList as $v1) {
      $str = $v1['base'].'-'.$v1['goods_id'];
      $store[$str] = $v1['id'];
    }
    $res = [];
    foreach ($data as $key => $v) {
      $arr = explode('-', $key);
      $res[] = [
        'id' => $store[$key],
        'base' => $arr[0],
        'goods_id' => $arr[1],
        'count' => $v,
      ];
    }

    // 导出数据
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(10);
    $sheet->getColumnDimension('B')->setWidth(10);
    $sheet->setCellValue('A1', 'id');
    $sheet->setCellValue('B1', 'count');
    $row = 1;
    foreach($res as $value){
      $row++;
      $sheet->setCellValue("A{$row}", $value['id']);
      $sheet->setCellValue("B{$row}", $value['count']);
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="bb.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 发货明细
   */
  public function dateOutExport(Request $request) {
    $startTime = $request->param('startTime', '');
    $endTime = $request->param('endTime', '');
    if (!$startTime || !$endTime) {
      return $this->send400Response('缺少日期');
    }
    $startTime = $startTime ? date('Y-m-d', strtotime($startTime)) : '';
    $endTime = $endTime ? date('Y-m-d', strtotime($endTime)) : '';
    $isNow = 0;
    // 是否当前日期
    if ($endTime === date('Y-m-d')) {
      $isNow = 1;
      // 获取当前库存
      $houseList = HouseService::getInstance()->getHouseJoinGoods();
      $houseList = array_column($houseList, null, 'goods_id');
    } else {
      // 获取当天库存
      $houseList = Db::table('goods')
        ->alias('g')
        ->field('hs.count,hs.total_cost as total_amount,g.id as goods_id,g.category_id,g.name,g.unit,g.sellable,g.price as sell_price,g.sale_name, g.conversion')
        ->join('house_statistics hs', 'g.id = hs.gid AND hs.date = '.$endTime, 'LEFT')
        ->select()
        ->toArray();
    }
    // 获取开始日期库存
    $houseStart = Db::table('house_statistics')
      ->where('date', '=', $startTime)
      ->select()
      ->toArray();
    $houseStart = array_column($houseStart, null, 'gid');
    // 获取所有门店出库
    $outCond = [
      ['o.status', 1, '='],
      ['o.purchase_time', $startTime, '>='],
      ['o.purchase_time', $endTime, '<=']
    ];
    $storeOut = InnerPurchaseService::getInstance()->getInnerJoinGoods($outCond);
    $outList = [];
    foreach ($storeOut as $out) {
      if (isset($outList[$out['goods_id']][$out['base']])) {
        $outList[$out['goods_id']][$out['base']] += $out['count'];
      } else {
        $outList[$out['goods_id']][$out['base']] = $out['count'];
      }
    }
    // 获取所有商品入库/生产
    $inCond = [
      ['o.status', 1, '='],
      ['o.default_time', $startTime, '>='],
      ['o.default_time', $endTime, '<='],
    ];
    $houseIn = HouseInService::getInstance()->getHouseInJoinGoods($inCond);
    $inList = [];
    foreach ($houseIn as $in) {
      if (isset($inList[$in['goods_id']])) {
        $inList[$in['goods_id']] += $in['count'];
      } else {
        $inList[$in['goods_id']] = $in['count'];
      }
    }
    // 获取所有的门店
    $store = UserService::getInstance()->storeAll();
    $store = array_column($store, 'basename', 'base');
    // 字母表
    $letter = [];
    $letterAll = [];
    for ($i=65; $i<91; $i++) {
      $letter[] = strtoupper(chr($i));
      for ($j=65; $j<91; $j++) {
        $letterAll[] = strtoupper(chr($i)).strtoupper(chr($j));
      }
    }
    $letter = array_merge($letter, $letterAll);
    $fields = ['名称', '单位'];
    $fields = array_merge($fields, $store, ['汇总', '工场库存', '余量', '备注']);
    $fieldsValue = ['name', 'unit'];
    $fieldsValue = array_merge($fieldsValue, array_keys($store), ['total', 'factory', 'rest', 'comment']);

    $data = [];

    foreach ($houseList as $house) {
      // 新增数据
      $add = [
        'name' => $house['name'],
        'unit' => $house['unit'],
        'total' => 0,
        'factory' => 0,
        'rest' => $house['count'],
        'comment' => ''
      ];

      foreach ($store as $k => $v) {
        $add[$k] = 0;
      }
      // 获取该产品出库
      $goodsId = $house['goods_id'];
      if (isset($outList[$goodsId])) {
        $detail = $outList[$goodsId];
        foreach ($detail as $dk => $d) {
          $add[$dk] = $d;
          $add['total'] += $d;
        }
      }
      // 获取产品前一天数据
      if (isset($houseStart[$goodsId])) {
        $add['factory'] = $houseStart[$goodsId]['count'];
      }
      // 获取该产品入库
      if (isset($inList[$goodsId])) {
        $add['factory'] += $inList[$goodsId];
      }
      // 当前数量
      $data[] = $add;
    }

    // 导出数据
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $count = count($fields);
    for ($i=0; $i<$count + 1; $i++) {
      if ($i === 0) {
        $sheet->getColumnDimension($letter[$i])->setWidth(15);
      } else {
        $sheet->getColumnDimension($letter[$i])->setWidth(10);
      }
    }
    // 第一行
    $sheet->mergeCells('A1:B1');
    $sheet->setCellValue('A1', '');
    $sheet->mergeCells('C1:'.$letter[3+($count - 7)/2].'1');
    $sheet->setCellValue('C1', '发货量');
    $sheet->mergeCells($letter[3+($count - 7)/2 + 1].'1:'.$letter[$count-5].'1');
    $sheet->setCellValue($letter[3+($count - 7)/2 + 1].'1', '发货时段：'.$startTime. ' - '. $endTime);

    foreach ($fields as $fk => $f) {
      $sheet->setCellValue($letter[$fk].'2', $f);
    }
    $row = 2;
    foreach($data as $dv){
      $row++;
      foreach ($fields as $fvk => $fv) {
//      $sheet->setCellValue("A{$row}", $dv['process']);
        $sheet->setCellValue($letter[$fvk].$row, $dv[$fieldsValue[$fvk]]);
      }
    }
    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="冠记明细-当日出仓明细表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

  /**
   * 进出库明细导出
   */
  public function houseInOut(Request $request){
    $type = $request->param('type', []);
    $process = $request->param('process', '');
    $base = $request->param('base', '');
    $startTime = $request->param('startTime', '');
    $endTime = $request->param('endTime', '');
    $startTime = $startTime ? date('Y-m-d', strtotime($startTime)) : '';
    $endTime = $endTime ? date('Y-m-d', strtotime($endTime) + (24 * 60 * 60)) : '';
    $list = [];
    // 采购或生产
    if ((!$type || in_array(1, $type) || in_array(2, $type)) && $base === '') {
      $condHouseIn = [];
      $condHouseIn[] = ['o.status', 1, '='];
      $processCon = [];
      if($type){
        if (in_array(1, $type)) {
          $processCon[] = 0;
        }
        if (in_array(2, $type)) {
          $processCon[] = 1;
        }
        $condHouseIn[] = ['g.process',$processCon,'IN'];
      }
      if ($startTime) {
        $condHouseIn[] = ['o.default_time', $startTime, '>='];
      }
      if ($endTime) {
        $condHouseIn[] = ['o.default_time', $endTime, '<'];
      }
      $houseInList = HouseInService::getInstance()->getHouseInJoinGoods($condHouseIn);
      foreach ($houseInList as $v1) {
        $list[] = [
          'time' => $v1['default_time'],
          'type' => $v1['process'] ? '生产' : '采购',
          'process' => $v1['process'] ? '成品' : '原料',
          'sell_number' => $v1['house_in_number'],
          'category_name' => $v1['category_name'],
          'name' => $v1['name'],
          'unit' => $v1['unit'],
          'count' => $v1['count'],
          'price' => $v1['price'],
          'total_amount' => $v1['total_amount'],
          'sell_price' => '',
          'total_price' => '',
          'basename' => '',
          'comment' => $v1['comment'],
          'datesort' => strtotime($v1['default_time'])
        ];
      }
    }
    // 出库
    if (!$type || in_array(3, $type)) {
      $condHouseOut = [];
      $condHouseOut[] = ['h.status', 1,'='];
      if ($process !== '') {
        $condHouseOut[] = ['h.process', $process, '='];
      }
      if ($base !== '') {
        $condHouseOut[] = ['h.base', $base, '='];
      }
      if ($startTime) {
        $condHouseOut[] = ['h.sale_time', $startTime, '>='];
      }
      if ($endTime) {
        $condHouseOut[] = ['h.sale_time', $endTime, '<'];
      }
      $houseOutList = HouseOutService::getInstance()->getHouseOutJoinGoods($condHouseOut);
      foreach ($houseOutList as $v2) {
        $list[] = [
          'time' => $v2['sale_time'],
          'type' => $v2['type'] === 1 ? '加工消耗' : '售出',
          'process' => $v2['process'] ? '成品' : '原料',
          'sell_number' => $v2['sell_number'],
          'category_name' => $v2['category_name'],
          'name' => $v2['name'],
          'unit' => $v2['unit'],
          'count' => $v2['count'],
          'price' => $v2['price'],
          'total_amount' => round($v2['count'] * $v2['price'], 2),
          'sell_price' => $v2['sell_price'],
          'total_price' => round($v2['count'] * $v2['sell_price'], 2),
          'basename' => $v2['basename'],
          'comment' => $v2['comment'],
          'datesort' => strtotime($v2['sale_time'])
        ];
      }
    }
    // 退货
    if (!$type || in_array(4, $type)) {
      $condBack = [];
      $condBack[] = ['so.status', 1,'='];
      if ($startTime) {
        $condBack[] = ['so.back_time', $startTime, '>='];
      }
      if ($endTime) {
        $condBack[] = ['so.back_time', $endTime, '<'];
      }
      if ($base !== '') {
        $condBack[] = ['sb.base', $base, '='];
      }
      if ($process !== '') {
        $condBack[] = ['g.process', $process, '='];
      }
      $backList = StoreBackService::getInstance()->getBackJoinStore($condBack);
      foreach ($backList as $v3) {
        $list[] = [
          'time' => $v3['back_time'],
          'type' => '退货',
          'process' => $v3['process'] ? '成品' : '原料',
          'sell_number' => $v3['apply_number'],
          'category_name' => $v3['category_name'],
          'name' => $v3['name'],
          'unit' => $v3['unit'],
          'count' => $v3['count'],
          'price' => $v3['price'] ? $v3['price'] : 0,
          'total_amount' => round($v3['count'] * $v3['price'], 2),
          'sell_price' => '',
          'total_price' => '',
          'basename' => $v3['basename'],
          'comment' => '',
          'datesort' => strtotime($v3['back_time'])
        ];
      }
    }
    // 排序
    foreach ($list as $key=>$value){
      $dateSort[$key] = $value['datesort'];
    }
    if ($list) {
      array_multisort($dateSort,SORT_NUMERIC,SORT_ASC, $list);
    }
    // 导出数据
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->getColumnDimension('A')->setWidth(10);
    $sheet->getColumnDimension('B')->setWidth(10);
    $sheet->getColumnDimension('C')->setWidth(20);
    $sheet->getColumnDimension('D')->setWidth(10);
    $sheet->getColumnDimension('E')->setWidth(10);
    $sheet->getColumnDimension('F')->setWidth(10);
    $sheet->getColumnDimension('G')->setWidth(10);
    $sheet->getColumnDimension('H')->setWidth(10);
    $sheet->getColumnDimension('I')->setWidth(10);
    $sheet->getColumnDimension('J')->setWidth(10);
    $sheet->getColumnDimension('K')->setWidth(10);
    $sheet->getColumnDimension('L')->setWidth(10);
    $sheet->getColumnDimension('M')->setWidth(10);
    $sheet->getColumnDimension('N')->setWidth(10);

    $sheet->setCellValue('A1', '日期');
    $sheet->setCellValue('B1', '操作');
    $sheet->setCellValue('C1', '单号');
    $sheet->setCellValue('D1', '类型');
    $sheet->setCellValue('E1', '分类');
    $sheet->setCellValue('F1', '商品名称');
    $sheet->setCellValue('G1', '单位');
    $sheet->setCellValue('H1', '数量');
    $sheet->setCellValue('I1', '成本');
    $sheet->setCellValue('J1', '总成本');
    $sheet->setCellValue('K1', '售价');
    $sheet->setCellValue('L1', '总售价');
    $sheet->setCellValue('M1', '流向');
    $sheet->setCellValue('N1', '备注');
    $row = 1;
    foreach($list as $value){
      $row++;
      $sheet->setCellValue("A{$row}", $value['time']);
      $sheet->setCellValue("B{$row}", $value['type']);
      $sheet->setCellValue("C{$row}", $value['sell_number']);
      $sheet->setCellValue("D{$row}", $value['process']);
      $sheet->setCellValue("E{$row}", $value['category_name']);
      $sheet->setCellValue("F{$row}", $value['name']);
      $sheet->setCellValue("G{$row}", $value['unit']);
      $sheet->setCellValue("H{$row}", $value['count']);
      $sheet->setCellValue("I{$row}", $value['price']);
      $sheet->setCellValue("J{$row}", $value['total_amount']);
      $sheet->setCellValue("K{$row}", $value['sell_price']);
      $sheet->setCellValue("L{$row}", $value['total_price']);
      $sheet->setCellValue("M{$row}", $value['basename']);
      $sheet->setCellValue("N{$row}", $value['comment']);
    }

    header("Content-type: application/octet-stream;charset=utf-8");
    header('Content-Disposition: attachment;filename="冠记-进出库明细表.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }
}